This file documents the SQL syntax of SQLdb

Copyright 1990-1993 Kyle Saunders

Permission is granted to freely distribute this file in its entirety as part of the SQLdb evaluation package.

SQLdb
*****

This file documents the March, 1993 alpha version of SQLdb.

Copying
*******

Copyright 1990-1993 Kyle Saunders

Permission is granted to freely distribute this file in its entirety as part of the SQLdb evaluation package.

Introduction
************

SQLdb SQL Language reference manual.  Everything in this manual applies only to SQLdb and any resemblence to other SQL dialects is purely coincidental.

Simple Elements
***************

Identifiers
===========

database-ref
------------

A database-ref is composed of a letter followed by zero or more characters from the set of numbers, letters, '_', '#'.  Thus, A#_o1ne is a legal database-ref, while _a#45 is not.

An database-ref cannot be a valid SQL keyword.

table-ref
---------

A table-ref is composed of a letter followed by zero or more characters from the set of numbers, letters, '_', '#'.  Thus, A#_o1ne is a legal table-ref, while _a#45 is not.

A table-ref cannot be a valid SQL keyword.

column-ref
----------

A column-ref is composed of a letter followed by zero or more characters from the set of numbers, letters, '_', '#'.  Thus, A#_o1ne is a legal column-ref, while _a#45 is not.

A column-ref cannot be a valid SQL keyword.

table-alias-ref
---------------

A table-alias-ref is composed of a letter followed by zero or more characters from the set of numbers, letters, '_', '#'.  Thus, A#_o1ne is a legal table-alias-ref, while _a#45 is not.

A table-alias-ref cannot be a valid SQL keyword.

column-alias-ref
----------------

A column-alias-ref is composed of a letter followed by zero or more characters from the set of numbers, letters, '_', '#'.  Thus, A#_o1ne is a legal column-alias-ref, while _a#45 is not.

A column-alias-ref cannot be a valid SQL keyword.

cursor-ref
----------

A cursor-ref is composed of a letter followed by zero or more characters from the set of numbers, letters, '_', '#'.  Thus, A#_o1ne is a legal cursor-ref, while _a#45 is not.

A cursor-ref cannot be a valid SQL keyword.

index-ref
---------

An index-ref is composed of a letter followed by zero or more characters from the set of numbers, letters, '_', '#'.  Thus, A#_o1ne is a legal index-ref, while _a#45 is not.

An index-ref cannot be a valid SQL keyword.

Literals
========

This section defines the literals of the various data types available.

integer
-------

An integer literal consists of one or more digits 0 through 9.

45332 is an example of an integer literal.

float
-----

A float literal consists of an integer literal (see integer), a period (decimal point), and another integer literal.

45.674 is an example of a float literal.  Note that .456 is not a valid float literal.

character
---------

A character literal consists of a string of characters enclosed in single quotes (').

'Hello, World' is an example of a character literal.

date
----

A date literal is a character literal (see character) with a special format. The format is 'mm/dd/yyyy', where mm is the month (1 is January, 12 is December), dd is the day of the month, and yyyy is the year.

'4/21/1993' is an example of a date literal.

time
----

A time literal is a character literal (see character) with a special format.  The format is 'hh:mm:ss', where hh is the hour (00 is midnight, 23 is 11pm), mm is minute (00-59), and ss is the second (00-59).

'17:35:01' is an example of a time literal.

Data Definition
***************

CREATE DATABASE
===============

CREATE DATABASE DATABASE-REF

Creates and opens specified database.  A database consists of a group of tables and global information that applies to those tables.  This command will fail if a database is already open.

See database-ref.

CREATE TABLE
============

CREATE TABLE TABLE-REF ( TABLE-DEF-ITEM-LIST )

This statement will create the specified table.  If a table already exists, it will be overwritten.  However, you cannot create a table is currently open.  The table will be automatically opened after creation.  You must have an open database (see CREATE DATABASE or see OPEN DATABASE), or this command will fail.

See table-ref. See table-def-item.

DROP TABLE
==========

DROP TABLE TABLE-REF

This statement will delete the specified table and remove it from the database.  The table must be currently open.

See table-ref.

CREATE INDEX
============

CREATE INDEX INDEX-REF ON TABLE-REF ( ORDER-REF-LIST )

This statement will create an index on the specified open table.  If the table contains any rows, they will be entered into the index.  Likewise, all new rows will be entered.  All index entries must be unique.  If any existing rows contain non-unique entries for the index, the command will fail.

See index-ref. See table-ref. See order-ref.

DROP INDEX
==========

DROP INDEX INDEX-REF ON TABLE-REF

This statement will delete the specified index from the specified open table.

See index-ref. See table-ref.

Data IO
*******

OPEN DATABASE
=============

OPEN DATABASE DATABASE-REF

Opens specified database.  Any tables that are created will be added to the currently open database.  This command will fail if a database is already open.

CLOSE DATABASE
==============

CLOSE DATABASE

Closes the currently open database.

OPEN TABLE
==========

OPEN TABLE TABLE-REF

This statement will open the specified table.  The must have been previously created, or this command will fail.

CLOSE TABLE
===========

CLOSE TABLE TABLE

This statement will close the specified table.  All non-system (ie user) tables must be closed before you may exit SQLdb.

Data Manipulation
*****************

query-spec
==========

QUERY-SPEC:
SELECT [ DISTINCT ] { SELECT-ITEM-LIST | * }
FROM BASETABLE-REF-LIST
[ WHERE SEARCH-CONDITION ]
[ GROUP BY GROUP-REF-LIST
[ HAVING SEARCH-CONDITION ] ]
[ ORDER BY ORDER-REF-LIST ]
[ OUTPUT TO FILE-NAME ]

This statement is used to retrieve information from the database.  It is made up of several clauses, which are explained in the following sections.

If DISTINCT is specified, then all duplicate rows will be removed from the result table.

The clauses are, in general, order sensitive.  For example, the OUTPUT TO clause should always be the last clause in the SELECT statement.

FROM
----

This clause specifies which tables SQLdb is supposed to gather information from.  Conceptually, SQLdb will generate a cartesian product of all the specfied tables.

See basetable-ref.

WHERE
-----

This clause is used by several other statements, and is used to restrict the set of rows to be operated on.  Let T be the result of the immediately preceding FROM clause.  The result of the WHERE clause is a table that is derived from T by eliminating all rows for which the SEARCH-CONDITION does not evaluate to TRUE.

See search-condition.

GROUP BY
--------

Let T be the result of the FROM clause and WHERE clause (if any).  Each "column" mentioned in the GROUP BY clause must be a column of T.  The result of the GROUP BY clause is a set of tables, derived from T by conceptually rearranging it into the minimum number of groups such that within any one group all rows have the same value for the combination of columns identified by the GROUP BY clause.

See group-ref.

HAVING
------

Let G be the grouped table result from the evaluation of the immediately preceding FROM clause, WHERE clause (if any), and GROUP BY clause.  The result of the HAVING clause are those groups for which the SEARCH-CONDITION is TRUE.

See search-condition.

ORDER BY
--------

This clause orders the result of the preceding clauses, including the projection of the SELECT-ITEM-LIST.  The rows are ordered according to the ORDER-REF-LIST, with each ORDER-REF being a progressively minor sort key, to break ties of the previous sort key.  All ORDER-REF's must refer to an item in the SELECT-ITEM-LIST.

See order-ref. See select-item.

OUTPUT TO
---------

This command will send the normal query output to the specified filename.

insert-spec
===========

INSERT-SPEC:
INSERT INTO TABLE-REF
[ ( COLUMN-REF-LIST ) ]
{ VALUES ( INSERT-ITEM-LIST ) |
QUERY-SPEC
}

This statement is used to put new rows into a table.  If the COLUMN-REF-LIST is omitted, it is equivalent to specifying all of the columns of the target table, in left-to-right order.  Any columns not named will a NULL value for each newly inserted row.

If a query is supplied instead of a value list, the query is executed and all rows in the result will be inserted into the table.

See table-ref. See column-ref. See insert-item. See query-spec.

delete-spec
===========

DELETE-SPEC:
DELETE FROM TABLE-REF
[ WHERE
{ SEARCH-CONDITION |
CURRENT OF CURSOR-REF
}
]

This statement is used to delete rows that satisfy the optional WHERE clause. If the WHERE clause is omitted, all rows in the table are deleted.  If a cursor is specified, then the row the cursor is on is deleted.

See table-ref. See cursor-ref. See search-condition.

update-spec
===========

UPDATE-SPEC:
UPDATE TABLE-REF
SET ASSIGNMENT-LIST
[ WHERE { search-condition |
CURRENT OF CURSOR-REF
}
]

This statement is used to modify existing rows that satisfy the optional WHERE clause.  If the WHERE clause is omitted, all rows in the table are modified.

See table-ref. See cursor-ref. See assignment. See search-condition.

Cursors
=======

The following statements can be type interactively, but they are intended to be used through an ARexx port.

DECLARE
-------

DECLARE CURSOR-REF CURSOR FOR QUERY-SPEC

This statement will create and define a cursor.  The cursor is positioned before the first row of the result table of the QUERY-SPEC.

See cursor-ref. See query-spec.

OPEN
----

OPEN CURSOR-REF

This statement will execute a previously declared cursor's QUERY-SPEC.  You must close every cursor that is opened.

FETCH
-----

FETCH { COLUMNS | FIRST | LAST |
PREVIOUS | NEXT | ABSOLUTE INTEGER |
RELATIVE INTEGER } OF CURSOR-REF

This statement will retrieve the specified row or column list, of the table created by the cursor's QUERY-SPEC.

If a column list is specified, it will return three elements for each column of the result table separated by a space: COLUMN-REF COLUMN-TYPE MAXIMUM-VALUE-SIZE

See column-ref.

CLOSE
-----

CLOSE CURSOR-REF

This statement will close and remove an existing cursor.  A cursor that has been closed must be re-declared and re-opened.

search-condition
****************

SEARCH-CONDITION:
SEARCH-ITEM |
SEARCH-ITEM { AND | OR }
SEARCH-ITEM

Each search-item evaluates to a true, false, AMARK, or IMARK.  This is a four value logic system.

Truth Tables:

AND| F  A  I  T		OR| F  A  I  T
---------------		--------------
F | F  F  F  F		F | F  A  F  T
A | F  A  I  A		A | A  A  A  T
I | F  I  I  I		I | F  A  I  T
T | F  A  I  T		T | T  T  T  T

See search-item.

search-item
===========

SEARCH-ITEM:
{ SEARCH-TEST |
{ NOT | MAYBE_A | MAYBE_I | MAYBE }
( SEARCH-CONDITION )
}

The logical operators NOT, MAYBE_A, MAYBE_I, MAYBE are defined below:

P |NOT P   P | MAYBE_I P   P | MAYBE_A P   P | MAYBE P
--------   -------------   -------------   -----------
t | f	   t | f	   t | f	   t | f
a | a      a | f           a | t	   a | t
i | i      i | t 	   i | f	   i | t
f | t      f | f 	   f | f	   f | f

See search-test.

search-test
-----------

SEARCH-TEST:
COMPARISON-TEST | LIKE-TEST |
SET-TEST | QUANTIFIED-TEST |
EXISTENCE-TEST | IS-NULL-TEST

comparison-test
---------------

COMPARISON-TEST:
EXPR
{ = | <> | < | <= | > | >= }
{ EXPR | QUERY-SPEC }

In the case of an QUERY-SPEC on the right-hand side, the EXPR is compared with the first element of the the first row of the result table.

See query-spec. See expr.

like-test
---------

LIKE-TEST:
COLUMN-REF [ NOT ] LIKE
STRING-PATTERN
[ ESCAPE CHARSTR ]

A string pattern is a string literal.  The '%' is a wildcard to match 0 or more characters, and the '_' is a wildcard to match one character. The escape character, if specified, will turn a '%' or '_' into a normal character.

Example:  foo LIKE 'ab#%c' ESCAPE '#' will match the string 'ab%c'.

See column-ref.

set-test
--------

SET-TEST:
EXPR [ NOT ] IN
{ ( LITERAL-LIST ) | QUERY-SPEC }

See expr. See Literals. See query-spec.

quantified-test
---------------

QUANTIFIED-TEST:
EXPR
{ = | <> | < | <= | > | >= }
{ ALL | ANY | SOME } QUERY-SPEC

See expr. See query-spec.

existence-test
--------------

existence-test:
[ NOT ] EXISTS QUERY-SPEC

is-null-test
------------

is-null-test:
IS [ NOT ] NULL

expr
----

EXPR:
EXPR-ITEM |
EXPR-ITEM { + | - | * | / } EXPR-ITEM

The four operators have equal precedence.  Use parentheses to force a particular evaluation order.

See expr-item.

expr-item
---------

EXPR-ITEM:
FUNCTION | VALUE | COLUMN-REF | ( EXPR )

See function. See value. See column-ref. See expr.

value
-----

VALUE:
LITERAL | AMARK | IMARK | NULL

See Literals.

function
--------

FUNCTION:
{ AVG | MAX | MIN | SUM | COUNT } ( expr )

Functions can only be applied to numeric (FLOAT, SMALLINT, and INTEGER) columns.

AVG		average of all values in an expression
MAX		maximum value in an expression
MIN		minimum value in an expression
SUM		total of values in an expression
COUNT		number of values (rows)

assignment
----------

ASSIGNMENT:
QUALCOLUMN-REF = EXPR

See qualcolumn-ref. See expr.

insert-item
-----------

INSERT-ITEM:
VALUE

See value.

select-item
-----------

SELECT-ITEM:
EXPR [ AS COLUMN-ALIAS-REF ]

Column aliases can be used to reference an expression later in the select-item-list.

Example:  SELECT col1,col1 AS foocol, foocol*2 AS bleh FROM footab;

col1  foocol  bleh
----  ------  ----
1     1       2
3     3       6

See expr. See column-alias-ref.

basetable-ref
-------------

BASETABLE-REF:
TABLE-REF [ TABLE-ALIAS-REF ]

See table-ref. See table-alias-ref.

qualcolumn-ref
--------------

QUALCOLUMN-REF:
[ { TABLE-REF | TABLE-ALIAS-REF } . ] COLUMN-REF

See table-ref. See table-alias-ref. See column-ref.

group-ref
---------

GROUP-REF:
QUALCOLUMN-REF | INTEGER

See qualcolumn-ref. See integer.

order-ref
---------

ORDER-REF:
GROUP-REF [ { ASC | DESC } ]

See group-ref.

table-def-item
--------------

TABLE-DEF-ITEM:
COLUMN-DEF

See column-def.

column-def
----------

COLUMN-DEF:
COLUMN-REF
{ INTEGER | SMALLINT |
FLOAT | CHAR(INTEGER) |
DATE | TIME
}

The maximum size of CHAR column is 1024.

See Literals.

Importing
=========

IMPORT ASCII
------------

IMPORT ASCII FILENAME
{ DELIMITER STRING |
TEMPLATE template-col-list
}

See template-col.

TEMPLATE-COL:
start-col:end-col

IMPORT DBASE
------------

Not implemented.

Misc
****

DISPLAY TABLE
=============

DISPLAY TABLE TABLE-REF

This statement will display information about the specified open table.

See table-ref.

DISPLAY DATABASE
================

DISPLAY DATABASE

This statement will display the names of all open tables.